package People;

import java.awt.Font;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Utilities {
	public static Connection connection;
	
	public static Font getGeorgia36() {
		return new Font("Georgia", Font.BOLD, 36);
	}

    public static Font getGeorgia20() {
    		return new Font("Georgia", Font.PLAIN, 20);
    }

    public static Font getGeorgia12() {
		return new Font("Georgia", Font.PLAIN, 12);
    }
    
    public static Font getGeorgia24() {
    		return new Font("Georgia", Font.PLAIN, 24);
    }

    public static void initDB() {
        loadDriver();
        connectDB();

        // if database vuoto
        //importSQL();    	
    }

    public static void loadDriver() {
	    	try {
	    		//Class.forName("org.sqlite.JDBC");
	    		Class.forName("com.mysql.jdbc.Driver");
	    	} catch(ClassNotFoundException e) {
	    		//System.out.println("Driver SQLite non trovato");
	    		System.out.println("Driver MySQL non trovato");
	    		System.exit(1);
	    	}
    }
    
    public static void connectDB() {
	    	try {
//	    		connection = DriverManager.getConnection("jdbc:sqlite:people.db");	
	    		connection = DriverManager.getConnection("jdbc:mysql://localhost/people", "root", "pwd");
	    	} catch(SQLException e) {
	    		System.out.println("Collegamento non riuscito.");
	    		System.exit(1);
	    	}
    }

    public static void createTables() {
	    	try {
			Statement s = connection.createStatement();
			s.executeUpdate("CREATE TABLE IF NOT EXISTS person (id INTEGER PRIMARY KEY AUTOINCREMENT," +
								"name TEXT NOT NULL," +
								"surname TEXT NOT NULL," +
								"bornDate TEXT," +
								"bornCity	TEXT," +
								"residence_street TEXT," +
								"residence_civicNumber TEXT," +
								"residence_city TEXT," +
								"domicile_street TEXT," +
								"domicile_civicNumber TEXT," + 
								"domicile_city TEXT," + 
								"maritalState INTEGER," + 
								"state INTEGER" + 
							");");
	
			s.executeUpdate("CREATE TABLE IF NOT EXISTS certificate (" + 
					  "id INTEGER PRIMARY KEY AUTOINCREMENT," +
					  "person INTEGER NOT NULL," +
					  "emissionDate TEXT," +
					  "type INTEGER NOT NULL," +
					  "path TEXT," +
					  "state INTEGER" +
					");");
	
			s.executeUpdate("CREATE TABLE IF NOT EXISTS certificate_types (" + 
					  "id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					  "text TEXT" + 
					");");
			
			s.executeUpdate("CREATE TABLE IF NOT EXISTS cities (" +
					  "id INTEGER PRIMARY KEY AUTOINCREMENT," +
					  "name TEXT NOT NULL," +
					  "CAP TEXT" +
					");");
			
			/*s.executeUpdate("INSERT INTO person (name, surname) VALUES ('Simone', 'Cainero')");
			s.executeUpdate("INSERT INTO person (name, surname) VALUES ('Maria Cristina', 'Danielis')");
			s.executeUpdate("INSERT INTO person (name, surname) VALUES ('Luciano', 'Cainero')");
			*/
			s.close();
	
	    	} catch(SQLException e) {
	    		System.out.println("Collegamento non riuscito");
	    		System.exit(1);
	    	}
    }
    
    public static void importSQL() {
        try {
        		String s = new String();  
            StringBuffer sb = new StringBuffer();  
            FileReader fr = new FileReader(new File("people.sql"));
            BufferedReader br = new BufferedReader(fr);  
  
            while((s = br.readLine()) != null)
                sb.append(s);  

            br.close();

            String[] inst = sb.toString().split(";");  
  
            Statement st = connection.createStatement();  
  
            for(int i = 0; i < inst.length; i++) {  
                if (!inst[i].trim().equals("")) {
                		st.executeUpdate(inst[i]);
                		System.out.println(">> "+inst[i]);  
                }  
            }
         } catch(Exception e) {  
                System.out.println("Error : " + e.toString());  
         }        
    }
    
	public static boolean isDateValid(String dateToValidate){
		 
		if(dateToValidate == null)
			return false;
 
		SimpleDateFormat sdf = new SimpleDateFormat("dd/mm/yyyy");
		sdf.setLenient(false);
 
		try {
 			Date date = sdf.parse(dateToValidate);
			System.out.println(date);
		} catch (ParseException e) {
            System.out.println("Error : " + e.toString());  
			return false;
		}
 
		return true;
	}
}
